<?php 
$req_path="./";
include_once($req_path."include/ini.php");
require_once($req_path."include/db.php");

class M_receive extends DB{
	private $dbConn;
	private $rowCount="0";
	private $ID="0";
	private $sqlSelect =" select  * from tb_receive_product r  Where 1=1 and IsDeleted = 0 " ;
	private $totalpage="0";
	
	function m_receive(){
		parent::__construct();
	}
	function getListPage(){
		global $list_page;
		return $list_page;
	}
	function getRunning($type='receive'){
		$sql ="select value from tb_running where name='".$type."' and r_date=current_date() " ;
		$result = $this->ExecuteData($sql);
		$cnt=$result->RecordCount();
		
		if ( $cnt > 0 ) {
			$row=$result->FetchRow();
			$sqlupdate="update tb_running set value=value+1 where name='".$type."'";
			$this->ExecuteData($sqlupdate);
			return $row["value"]+1;	
		}else{
			$sqlupdate="update tb_running set value=1 , r_date=current_date() where name='".$type."'";
			$this->ExecuteData($sqlupdate);
			return 1;
		}
	 
	}
	function getData( $code=null ,$supplierid=0, $receivedatefrom=null, $receivedateto=null, $status=2 ,$pageStart=1){
		global $list_page;
		$sqlscript= "select r.ID , r.Code , s.SupplierName, coalesce(DATE_FORMAT( ReceivedDate, '%d/%m/%Y' ),'') ReceivedDate , ReceivedBy "
				." , CreditTerm , Case when r.Status=0 then 'Check' else 'Closed' end Status ,"
				." Case when r.Status=0 then r.Amount_rq else r.Amount_rc end Amount ,Total, "
				."(

					SELECT CASE WHEN sum( Amount_rq ) = sum( Amount_rc ) 
					THEN 0 
					ELSE 1 
					END flag
					FROM tb_receive_product_detail rd
					WHERE rd.Receive = r.ID
					) AS flag "
				." from tb_receive_product r left join tb_supplier s on r.Supplier=s.ID Where 1=1 and r.IsDeleted = 0  ";
		
		if (isset($code) && $code!=""){
			$sqlscript.= " and r.Code = '". $code."'";
		}
		if (isset($supplierid) && $supplierid!="0"){
			$sqlscript.= " and r.Supplier = ". $supplierid;
		}
		if (isset($receivedatefrom) && $receivedatefrom!=""){
			list($day, $month, $year) = split('/', $receivedatefrom);
			$Rfrom= $year."/".$month."/".$day;
			list($day, $month, $year) = split('/', $receivedateto);
			$Rto= $year."/".$month."/".$day;
			
			$sqlscript.= " and r.ReceivedDate Between '". $Rfrom."' and '".$Rto."'";
		}
		if (isset($status) && $status!=2){
			$sqlscript.= " and r.Status = ". $status;
		}
		//print_r($sqlscript);
		//Order 
		$sqlscript.= " order by ReceivedDate Desc , r.Status" ;
		//Execute
	 
		$result = $this->ExecuteData($sqlscript);
		//Get all rows
		$rows =$result->RecordCount();
	
		//Page limit
		$sqlscript.= " limit ". ($pageStart-1)*$list_page. ", " . $list_page;
		//paging
		$result = $this->ExecuteData($sqlscript);		
	
		$totalpage=1;
		if ($rows>0){
			$rt = $rows%$list_page;
			
			if($rt!=0) {
				$totalpage = floor($rows/$list_page)+1;
			}
			else {
				$totalpage = floor($rows/$list_page);
			}
		}
		
		$this->rowCount=$rows;
		$this->totalpage=$totalpage;	
		
		return $result;
	}
	
	function getProductDetail($id=0, $amt=0,$price=0){
		$sqlscript= " Select p.ID, ProductName , u.Unit, $price as Price ,".$amt ." as Amount , $price * $amt as Total ";
		$sqlscript.= " from tb_product p ";
		$sqlscript.= " join tb_units u on p.MainUnit=u.ID where IsDeleted=0 ";
		$sqlscript.= " and p.ID='". $id."'";
		$result = $this->ExecuteData($sqlscript);
		return $result->FetchRow();
	}
	//for button click
	function getEditData($id){
		$sqlscript= "select r.ID , r.Code, r.Supplier ,r.Total "
				." from tb_receive_product r "
				." Where 1=1 and IsDeleted = 0 "
				." and r.ID=".$id ;
				
		$result = $this->ExecuteData($sqlscript);	
				
		return $result->FetchRow();
	}
	
	function getEditDataDetail($id){
		$sqlscript = " Select p.ID, ProductName , u.Unit, d.Price ,d.Amount_rq as Amount , d.Price , d.Total ";
		$sqlscript.= " from tb_receive_product_detail d  ";
		$sqlscript.= " left join tb_product p  on d.Product=p.ID";
		$sqlscript.= " left join tb_units u on p.MainUnit=u.ID where 1=1 ";
		$sqlscript.= " and d.Receive=".$id ;
		$result2 = $this->ExecuteData($sqlscript);
		return $result2 ;
	}
	////////////////////////////////////////
	
	//for Check button click
	function getDataDetail($id){
		$sqlscript= "select r.ID , r.Code, r.Supplier , s.SupplierName, coalesce(ReceivedBy,'-') as ReceivedBy "
				." , coalesce(DATE_FORMAT( r.ReceivedDate, '%d/%m/%Y' ),'-') as ReceivedDate "
				." , coalesce(DATE_FORMAT( r.CreatedDate, '%d/%m/%Y' ),'-') as CreatedDate  "
				." , CreditTerm , Case when r.Status=0 then 'Check' else 'Closed' end Status ,"
				." Case when r.Status=0 then r.Amount_rq else r.Amount_rc end Amount ,Total "
				." from tb_receive_product r left join tb_supplier s on r.Supplier=s.ID Where 1=1 and r.IsDeleted = 0   "
				." and r.ID=".$id;
				
		$result = $this->ExecuteData($sqlscript);		
		return $result->FetchRow();
	}
	
	function ReceiveViewDetail($id,$pageStart=1,$mode='d'){
		global $list_page;
		$sqlscript= " Select r.ID, p.ID as ProductID , p.ProductName , c.CatName, t.Type as ProductType "
				.", u.Unit , r.Price , r.Amount_rq , r.Amount_rc "
				.", Case when rp.Status=0 then r.Amount_rq else r.Amount_rc end Amount "
				.", (Case when rp.Status=0 then r.Amount_rq else r.Amount_rc end) * r.Price as Total "
				." from tb_receive_product_detail r "
				." join tb_receive_product rp on rp.ID=r.Receive"
				." left join tb_product p on p.ID=r.Product "
				." left join tb_product_category c on c.CatID = p.Category "
				." left join tb_product_type t on t.ID=p.Type "
				." left join tb_units u on u.ID=p.MainUnit "
				." where   rp.IsDeleted = 0  and  r.Receive=".$id
				." order by p.ProductName asc " ;
		//Execute
		 
		$result = $this->ExecuteData($sqlscript);
		//Get all rows
		$rows =$result->RecordCount();		
		//Page limit
		//$sqlscript.= " limit ". ($pageStart-1)*$list_page. ", " . $list_page;
		//paging
		
		if ($mode=='d'){
			$result = $this->ExecuteData($sqlscript);		
		}
	
		$totalpage=1;
		if ($rows>0){
			$rt = $rows%$list_page;
			
			if($rt!=0) {
				$totalpage = floor($rows/$list_page)+1;
			}
			else {
				$totalpage = floor($rows/$list_page);
			}
		}
		
		$this->rowCount=$rows;
		$this->totalpage=$totalpage;	
		
		return $result;		
	}
	function Excute($sql){
		$this->ExecuteData($sql);
	}
	function getRowCount(){
		return $this->rowCount;
	}
	
	function getLastInsert(){
		return $this->ID;
	}
	
	function InsertData($val){
		if (isset($val)){
			$sqlInsert=$this->InsertSqlScript($this->sqlSelect, $val);
			//return $sqlInsert;
			$this->ExecuteData($sqlInsert);
			$this->ID = $this->getLastID();
			return $this->ID;
		}
	}
	function updateData($sql, $val){
		if(isset($val)){
			$sqlUpdate=$this->UpdateSqlScript($sql,$val);
			return $this->ExecuteData($sqlUpdate);
		}
	}
	function delData($id , $CreateUser){
		if(isset($id)){
			$sqlDelete = "update  tb_receive_product set IsDeleted = 1 , UpdatedDate =NOW(), UpdatedBy='".$CreateUser."' where ID='".$id."'";
			$this->ExecuteData($sqlDelete);
			//delete detail
			//$this->delDataDetail($id);
			return "c";
		}
	}
	function delDataDetail($id){
			$sqlDelete = "delete from tb_receive_product_detail where Receive='".$id."'";
			$this->ExecuteData($sqlDelete);
	}
	function getTotalPage(){
		return $this->totalpage;
	}
	function getDataCBO($t="c"){
		if ($t=="s")
			$sqlscript= "select ID as ID, SupplierName as Name from tb_supplier where IsActived=1 order by SupplierName ";
		else if ($t=="p"){
			$sqlscript= "select ID as ID, ProductName as Name from tb_product p where 1=1 and IsDeleted=0 and p.Type=1 order by ProductName";
			
			$result = $this->ExecuteData($sqlscript);
			
			while($row = $result->FetchRow()){
				$ar[$row["Name"]] = $row["ID"];
			}
		 
			return $ar;			
		}
		
		$result = $this->ExecuteData($sqlscript);
		
		 while($row = $result->FetchRow()){
			$ar[$row["ID"]] = $row["Name"];
		 }
		 
		return $ar;
	}
	//update amount after check completed
	function UpdateReceiveProduct($id, $amt=0){
		$sql=" Update tb_receive_product_detail Set ";
		$sql.=" Amount_rc =".$amt;
		$sql.=" Where ID=".$id; 
		
		$this->ExecuteData($sql);
	} 
	//Manage Stock
	function UpdateStock($pid, $branchid, $amt=0, $user=""){
		// Exist update
		$cMulti = $this->convertStock($pid);
		if ($this->checkStock($pid,$branchid)){
			$sqlupdate=" update tb_stock_product "
						. " set Amount= Amount+".( $amt * $cMulti)
						. " , MainAmt= ((Amount) - ((Amount)%".$cMulti ."))/".$cMulti 
						. " , UpdatedDate='".date( "Y/m/d H:i:s" )."'"
						. " , UpdatedBy='".$user."'"
						. " Where BranchID=".$branchid //HO
						. " And ProductID=".$pid;
			$this->ExecuteData($sqlupdate);
 		}else{ // not exists insert
			$sqlInsert=" Insert into tb_stock_product(BranchID, ProductID, Amount,MainAmt ,UpdatedDate, UpdatedBy)";
			$sqlInsert.="values (".$branchid.",".$pid.",".( $amt * $cMulti).",".$amt.",Now(),'".$user."') ";
			$this->ExecuteData($sqlInsert);
		}
	} 
	function updateCostPrice($pid,$cost=0){
		$sqlUpdate="Update tb_Product set Cost_Price=".$cost." where ID='".$pid."'";
		$this->ExecuteData($sqlUpdate);
	}
	function convertStock($pid){
		$sql = 'SELECT coalesce(Scale ,1) Scale
				FROM tb_product
				Where ID ='.$pid.' and SubUnit <> 0';
				
		$result=$this->ExecuteData($sql);
		$count= $result->RecordCount();
		if ($count>0){
			$row=$result->FetchRow();
			return $row['Scale'];
		}else{
			return 1;
		}
	}
	//Check
	function checkStock($pid=0, $branchid=0){
		$sql = " select * from tb_stock_product where BranchID=".$branchid." and ProductID=".$pid;
		$result = $this->ExecuteData($sql);
		$count=$result->RecordCount();
		return $count>0?true:false;
	}
}
?>